package com.company.project.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.company.project.entity.VaccinationStatisticsEntity;
import com.company.project.vo.req.VaccinationStatisticsTdVO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * ${comments}
 *
 * @author jpy
 * @email *****@mail.com
 * @date 2021年4月13日11点00分
 */
public interface VaccinationStatisticsMapper extends BaseMapper<VaccinationStatisticsEntity> {
    @Select("SELECT JD.xzqh, " +
            "       JD.mc, " +
            "       JD.jb, " +
            "       SUM(JD.LJSL) as LJSL, " +
            "       SUM(JD.LJDYZ) as LJDYZ, " +
            "       SUM(JD.LJDRZ) AS LJDRZ, " +
            "       SUM(JD.LJDSZ) AS LJDSZ, " +
            "       SUM(JD.DRSL) AS DRSL, " +
            "       SUM(JD.DRDYZ) AS DRDYZ, " +
            "       SUM(JD.DRDRZ)+SUM(JD.DRDSZ) AS DRDRZ, " +
            "       SUM(JD.DRDSZ) AS DRDSZ " +
            "  FROM (select t.xzqh," +
            "               m.mc," +
            "               m.jb," +
            "               count(*) as ljsl," +
            "               sum(decode(t.stitches, '01', 1, 0)) as LJDYZ," +
            "               sum(decode(t.stitches, '02', 1, 0)) AS LJDRZ," +
            "               sum(decode(t.stitches, '03', 1, 0)) AS LJDSZ," +
            "               0 AS DRSL," +
            "               0 AS DRDYZ," +
            "               0 AS DRDRZ," +
            "               0 AS DRDSZ" +
            "          from (select t.xzqh," +
            "                       t.identity," +
            "                       to_char(t.create_time, 'yyyymmdd')," +
            "                       t.township," +
            "                       t.stitches" +
            "                  from CXGWJOB.VACCINATION t" +
            "                 where to_char(t.create_time, 'yyyy-mm-dd') <=" +
            "                        #{enddate, jdbcType=VARCHAR}  " +
            "                 group by t.xzqh," +
            "                          t.identity," +
            "                          to_char(t.create_time, 'yyyymmdd')," +
            "                          t.township," +
            "                          t.stitches) t" +
            "          left join CXGWJOB.e_xzqh m" +
            "            on t.xzqh = substr(m.dm, 1, 9)" +
            "           and m.jb in ('1', '3')" +
            "         group by t.xzqh, m.mc, m.jb" +
            "" +
            "        UNION ALL" +
            "" +
            "        select t.xzqh," +
            "               m.mc," +
            "               m.jb," +
            "               0 AS LJSL," +
            "               0 AS LJDYZ," +
            "               0 AS LJDRZ," +
            "               0 AS LJDSZ," +
            "               count(*) as DRSL," +
            "               sum(decode(t.stitches, '01', 1, 0)) as DRDYZ," +
            "               sum(decode(t.stitches, '02', 1, 0)) AS DRDRZ," +
            "               sum(decode(t.stitches, '03', 1, 0)) AS DRDSZ" +
            "          from (select t.xzqh," +
            "                       t.identity," +
            "                       to_char(t.create_time, 'yyyymmdd')," +
            "                       t.township," +
            "                       t.stitches" +
            "                  from CXGWJOB.VACCINATION t" +
            "                 where to_char(t.create_time, 'yyyy-mm-dd') =" +
            "                      #{enddate, jdbcType=VARCHAR}   " +
            "                 group by t.xzqh," +
            "                          t.identity," +
            "                          to_char(t.create_time, 'yyyymmdd')," +
            "                          t.township," +
            "                          t.stitches) t" +
            "          left join CXGWJOB.e_xzqh m" +
            "            on t.xzqh = substr(m.dm, 1, 9)" +
            "           and m.jb in ('1', '3')" +
            "         group by t.xzqh, m.mc, m.jb) JD" +
            " GROUP BY JD.xzqh, JD.mc, JD.jb" +
            "  order by LJSL asc, JD.jb "
    )
    List<VaccinationStatisticsEntity> qListByDate(  @Param("enddate")String enddate );

    //村 根据日期查询
    @Select("select XZC.xzqh, " +
            "       XZC.JD,   " +
            "       XZC.township," +
            "       XZC.CUN as mc," +
            "       SUM(XZC.LJSL) as LJSL," +
            "       SUM(XZC.LJDYZ) as LJDYZ, " +
            "       SUM(XZC.LJDRZ) AS LJDRZ, " +
            "       SUM(XZC.LJDSZ) AS LJDSZ," +
            "       SUM(XZC.DRSL) AS DRSL," +
            "       SUM(XZC.DRDYZ) AS DRDYZ," +
            "       SUM(XZC.DRDRZ)+SUM(JD.DRDSZ) AS DRDRZ," +
            "       SUM(XZC.DRDSZ) AS DRDSZ " +
            "  from (select t.xzqh," +
            "               m.mc as jd," +
            "               substr(t.township, 1, 12) as township," +
            "               n.mc as cun," +
            "               count(*) as ljsl," +
            "               sum(decode(t.stitches, '01', 1, 0)) as ljDYZ," +
            "               sum(decode(t.stitches, '02', 1, 0)) AS ljDRZ," +
            "               sum(decode(t.stitches, '03', 1, 0)) AS ljDSZ," +
            "               0 AS DRSL," +
            "               0 AS DRDYZ," +
            "               0 AS DRDRZ," +
            "               0 AS DRDSZ" +
            "          from (select t.xzqh," +
            "                       t.identity," +
            "                       to_char(t.create_time, 'yyyymmdd')," +
            "                       t.township," +
            "                       t.stitches" +
            "                  from CXGWJOB.VACCINATION t" +
            "                 where to_char(t.create_time, 'yyyy-mm-dd') <=" +
            "                       #{enddate, jdbcType=VARCHAR}        " +
            "                   and t.xzqh = #{jurisdiction, jdbcType=VARCHAR}       " +
            "                 group by t.xzqh," +
            "                          t.identity," +
            "                          to_char(t.create_time, 'yyyymmdd')," +
            "                          t.township," +
            "                          t.stitches) t" +
            "          left join CXGWJOB.e_xzqh m" +
            "            on t.xzqh = substr(m.dm, 1, 9)" +
            "           and m.jb in ('1', '3')" +
            "          left join CXGWJOB.e_xzqh n" +
            "            on substr(t.township, 1, 12) = n.dm" +
            "         group by t.xzqh, m.mc, substr(t.township, 1, 12), n.mc" +
            "" +
            "        union all" +
            "" +
            "        select t.xzqh," +
            "               m.mc as jd," +
            "               substr(t.township, 1, 12) as township," +
            "               n.mc as cun," +
            "               0 AS LJSL," +
            "               0 AS LJDYZ," +
            "               0 AS LJDRZ," +
            "               0 AS LJDSZ," +
            "               count(*) as DRSL," +
            "               sum(decode(t.stitches, '01', 1, 0)) as DRDYZ," +
            "               sum(decode(t.stitches, '02', 1, 0)) AS DRDRZ," +
            "               sum(decode(t.stitches, '03', 1, 0)) AS DRDSZ" +
            "          from (select t.xzqh," +
            "                       t.identity," +
            "                       to_char(t.create_time, 'yyyymmdd')," +
            "                       t.township," +
            "                       t.stitches" +
            "                  from CXGWJOB.VACCINATION t" +
            "                 where to_char(t.create_time, 'yyyy-mm-dd') =" +
            "                       #{enddate, jdbcType=VARCHAR}            " +
            "                   and t.xzqh =  #{jurisdiction, jdbcType=VARCHAR}                 " +
            "                 group by t.xzqh," +
            "                          t.identity," +
            "                          to_char(t.create_time, 'yyyymmdd')," +
            "                          t.township," +
            "                          t.stitches) t" +
            "          left join CXGWJOB.e_xzqh m" +
            "            on t.xzqh = substr(m.dm, 1, 9)" +
            "           and m.jb in ('1', '3')" +
            "          left join CXGWJOB.e_xzqh n" +
            "            on substr(t.township, 1, 12) = n.dm" +
            "         group by t.xzqh, m.mc, substr(t.township, 1, 12), n.mc) XZC" +
            " group by XZC.XZQH, XZC.JD, XZC.township, XZC.CUN" +
            " ORDER BY LJSL asc ")
    List<VaccinationStatisticsEntity> cListBydate(@Param("jurisdiction")String jurisdiction  , @Param("enddate")String enddate);


    @Select(" select substr(dm, 1, 9) mc from e_xzqh where mc = #{enddate, jdbcType=VARCHAR}    ")
    List<VaccinationStatisticsTdVO> todayCount(@Param("enddate")String enddate );

}
